package cmp.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cmp.to.ComplaintTO;
import cmp.to.DeptTO;
import cmp.to.LoginResponseTO;
import cmp.to.ReportTO;
import cmp.to.UserTO;

public class DatabaseManager {

	public static Connection getConnection() {

		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			/*
			 * conn = DriverManager.getConnection("jdbc:mysql://localhost/test?"
			 * + "user=sqluser&password=sqluserpw");
			 */
			conn = DriverManager.getConnection("jdbc:mysql://localhost/complaintdb?" + "user=user&password=1234");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	/*
	 * public String getPwd(String id) { Connection conn = getConnection();
	 * String pwd = null; try { PreparedStatement st =
	 * conn.prepareStatement("select pwd from users where uid = ?");
	 * st.setString(1, id); st.execute(); ResultSet rs = st.executeQuery();
	 * 
	 * if (rs != null && rs.next()) { pwd = rs.getString("pwd"); }
	 * 
	 * } catch (SQLException e) { // TODO Auto-generated catch block
	 * e.printStackTrace(); } System.out.println("db res = " + pwd); return pwd;
	 * }
	 */

	public LoginResponseTO getLogin(String id, String pwd) {
		LoginResponseTO to = new LoginResponseTO();
		Connection conn = getConnection();
		try {
			String res = null;

			to.setLogin("false");
			PreparedStatement st = conn.prepareStatement("select uid,pwd,name from users where uid = ?");

			st.setString(1, id);
			st.execute();
			ResultSet rs = st.executeQuery();
			if (rs != null && rs.next()) {
				res = rs.getString("pwd");
				if (pwd.equals(res)) {
					to.setLoginName(rs.getString("name"));
					to.setId(rs.getInt("uid"));
					to.setLogin("true");
					to.setLoginType("user");

					st = conn.prepareStatement("select uid from is_admin where uid = ?");
					st.setInt(1, to.getId());
					rs = st.executeQuery();
					if (rs.next()) {
						to.setLoginType("admin");
					}

				}
			} else {
				st = conn.prepareStatement("select dept_pwd, did, dept_name from department where did = ?");
				st.setString(1, id);
				st.execute();
				rs = st.executeQuery();
				if (rs != null && rs.next()) {
					res = rs.getString("dept_pwd");
					if (pwd.equals(res)) {
						to.setLoginName(rs.getString("dept_name"));
						to.setId(rs.getInt("did"));
						to.setLogin("true");
						to.setLoginType("dept");
					}
				}
				rs.close();

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("db res = " + pwd);
		return to;
	}

	public UserTO createUser(UserTO to) {
		String insertQuery = "INSERT INTO `users` (`uid`,`name`,`dob`,`address`,`phone_no`,`pwd`) VALUES (default, ?, ?, ?, ?, ?)";
		String fetchIdQuery = "select LAST_INSERT_ID()";
		Connection conn = getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(insertQuery);
			ps.setString(1, to.getName());
			ps.setDate(2, to.getDob());
			ps.setString(3, to.getAddress());
			ps.setString(4, to.getPhone_no());
			ps.setString(5, to.getPwd());

			int i = ps.executeUpdate();

			if (i != 1)
				return null;

			ps = conn.prepareStatement(fetchIdQuery);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				int uid = rs.getInt(1);
				to.setUid(uid);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return to;
	}

	public DeptTO createDept(DeptTO to) {

		String insertQuery = "INSERT INTO `department` (`did`,`dept_name`,`dept_pwd`) VALUES (default , ?, ? )";
		String fetchIdQuery = "select LAST_INSERT_ID()";
		Connection conn = getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(insertQuery);
			ps.setString(1, to.getDept_name());
			ps.setString(2, to.getDept_pwd());

			int i = ps.executeUpdate();

			if (i != 1)
				return null;

			ps = conn.prepareStatement(fetchIdQuery);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				int uid = rs.getInt(1);
				to.setDid(uid);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return to;
	}

	public ArrayList<DeptTO> getDepartments() {

		Connection conn = getConnection();
		ArrayList<DeptTO> depts = null;
		DeptTO to = null;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from department");

			if (rs != null) {
				depts = new ArrayList<>();
				while (rs.next()) {
					to = new DeptTO();
					/*
					 * System.out.println("did : " + rs.getInt("id"));
					 * System.out.println("user : " + rs.getString("myuser"));
					 */
					to.setDid(rs.getInt("did"));
					to.setDept_name(rs.getString("dept_name"));
					depts.add(to);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return depts;
	}

	public String createComplaint(ComplaintTO to) {
		String query = "INSERT INTO `complaints` (`cid`,`did`,`uid`,`location`,`date_submission`,`date_resolution`,`description`,`status`) VALUES (default, ?, ?, ?, ?, ?, ?, ?);";
		String status = "false";
		Connection conn = getConnection();

		try {
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, to.getDid());
			st.setInt(2, to.getUid());
			st.setString(3, to.getLocation());
			st.setTimestamp(4, to.getDate_submission());
			st.setString(5, null);
			st.setString(6, to.getDescription());
			st.setString(7, "OPEN");
			int result = st.executeUpdate();

			if (result == 1) {
				System.out.println("1 row inserted succesfully");
				status = "true";
			}

		}catch (SQLException e) {
			System.out.println("msg : " + e.getMessage() );
			if("already 10 entries".equalsIgnoreCase(e.getMessage())){
				status = "Open complaint limit reached";
			}
			e.printStackTrace();
		}
		return status;

	}

	public boolean updateComplaint(ComplaintTO to) {
		String query = "UPDATE complaints SET `description`= ?, `did`= ?, `location`= ? WHERE  `cid`= ?;";
		boolean status = false;
		Connection conn = getConnection();

		try {
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, to.getDescription());
			st.setInt(2, to.getDid());
			st.setString(3, to.getLocation());
			st.setInt(4, to.getCid());

			int result = st.executeUpdate();

			if (result == 1) {
				System.out.println("1 row updated succesfully");
				status = true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return status;

	}

	public boolean resolveComplaint(ComplaintTO to) {
		String query = "update complaints set status = 'resolved', date_resolution = ? where cid = ?";
		boolean status = false;
		Connection conn = getConnection();

		try {
			PreparedStatement st = conn.prepareStatement(query);
			st.setTimestamp(1, to.getDate_resolution());
			st.setInt(2, to.getCid());

			int result = st.executeUpdate();

			if (result == 1) {
				System.out.println("1 row resolved succesfully");
				status = true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return status;

	}

	public ArrayList<ComplaintTO> getComplaints(String field, String key, int id, String userType) {

		Connection conn = getConnection();
		PreparedStatement ps = null;

		ArrayList<ComplaintTO> compls = new ArrayList<>();
		ComplaintTO to = null;

		try {
			ResultSet rs = null;
			if (userType.equalsIgnoreCase("user")) {
				ps = conn.prepareStatement("select * from complaints where uid = ?");
				ps.setInt(1, id);

			} else {
				ps = conn.prepareStatement("select * from complaints where did = ? and status like ?");
				ps.setInt(1, id);
				ps.setString(2, key);

			}

			rs = ps.executeQuery();
			// ResultSet rs = st.executeQuery("select * from complaints");

			while (rs.next()) {
				to = new ComplaintTO();
				to.setCid(rs.getInt("cid"));
				to.setDate_resolution(rs.getTimestamp("date_resolution"));
				to.setDate_submission(rs.getTimestamp("date_submission"));
				to.setDescription(rs.getString("description"));
				to.setDid(rs.getInt("did"));
				to.setUid(rs.getInt("uid"));
				to.setLocation(rs.getString("location"));
				to.setStatus(rs.getString("status"));
				to.setDid(rs.getInt("did"));

				compls.add(to);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compls;

	}

	public ComplaintTO getComplaintById(int cid) {

		Connection conn = getConnection();
		PreparedStatement ps = null;

		ComplaintTO to = null;

		try {
			ResultSet rs = null;

			ps = conn.prepareStatement("select * from complaints where cid = ?");
			ps.setInt(1, cid);

			rs = ps.executeQuery();
			// ResultSet rs = st.executeQuery("select * from complaints");

			if (rs.next()) {
				to = new ComplaintTO();
				to.setCid(rs.getInt("cid"));
				to.setDate_resolution(rs.getTimestamp("date_resolution"));
				to.setDate_submission(rs.getTimestamp("date_submission"));
				to.setDescription(rs.getString("description"));
				to.setDid(rs.getInt("did"));
				to.setUid(rs.getInt("uid"));
				to.setLocation(rs.getString("location"));
				to.setStatus(rs.getString("status"));
				to.setDid(rs.getInt("did"));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return to;

	}

	public ArrayList<ComplaintTO> searchComplaints(String column, String value) {

		StringBuilder sb = new StringBuilder("select * from complaints where ");
		if (column.equalsIgnoreCase("all")) {
			sb.append(" cid like '%" + value + "%' or did like '%" + value + "%' or uid like '%" + value + "%' or location like '%" + value
					+ "%' " + "or date_submission like '%" + value + "%' or date_resolution like '%" + value + "%' or description like '%"
					+ value + "%' or status like '%" + value + "%' ");
		} else if (column.equalsIgnoreCase("cid")) {
			sb.append(" cid like '%" + value + "%'");
		} else if (column.equalsIgnoreCase("did")) {
			sb.append("did like '%" + value + "%'");
		} else if (column.equalsIgnoreCase("uid")) {
			sb.append("uid like '%" + value + "%");
		} else if (column.equalsIgnoreCase("location")) {
			sb.append("location like '%" + value + "%'");
		} else if (column.equalsIgnoreCase("date_submission")) {
			sb.append("date_submission like '%" + value + "%'");
		} else if (column.equalsIgnoreCase("date_resolution")) {
			sb.append("date_resolution like '%" + value + "%'");
		} else if (column.equalsIgnoreCase("description")) {
			sb.append("description like '%" + value + "%'");
		} else if (column.equalsIgnoreCase("status")) {
			sb.append("status like '%" + value + "%'");
		}

		Connection conn = getConnection();
		PreparedStatement ps = null;

		ArrayList<ComplaintTO> compls = new ArrayList<>();
		ComplaintTO to = null;

		try {
			ResultSet rs = null;

			ps = conn.prepareStatement(sb.toString());

			rs = ps.executeQuery();
			// ResultSet rs = st.executeQuery("select * from complaints");

			while (rs.next()) {
				to = new ComplaintTO();
				to.setCid(rs.getInt("cid"));
				to.setDate_resolution(rs.getTimestamp("date_resolution"));
				to.setDate_submission(rs.getTimestamp("date_submission"));
				to.setDescription(rs.getString("description"));
				to.setDid(rs.getInt("did"));
				to.setUid(rs.getInt("uid"));
				to.setLocation(rs.getString("location"));
				to.setStatus(rs.getString("status"));
				to.setDid(rs.getInt("did"));

				compls.add(to);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compls;

	}

	public ArrayList<ReportTO> getReportByDept() {

		Connection conn = getConnection();
		ArrayList<ReportTO> report = null;
		ReportTO to = null;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select dept_name, count(*) as complaint_count from complaints join department "
					+ "where complaints.did = department.did group by complaints.did order by complaint_count desc;");

			if (rs != null) {
				report = new ArrayList<>();
				while (rs.next()) {
					to = new ReportTO();
					/*
					 * System.out.println("did : " + rs.getInt("id"));
					 * System.out.println("user : " + rs.getString("myuser"));
					 */
					to.setLabel(rs.getString("dept_name"));
					to.setValue(rs.getInt("complaint_count"));
					report.add(to);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return report;
	}

	public ArrayList<ReportTO> getReportByResolutionTime() {

		Connection conn = getConnection();
		ArrayList<ReportTO> report = null;
		ReportTO to = null;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT department.did, dept_name, avg(TIMESTAMPDIFF(minute, date_submission, date_resolution)) as diff from complaints, department " 
							+ "where complaints.did = department.did and date_resolution is not null and date_submission is not null "
							+ " and TIMESTAMPDIFF(minute, date_submission, date_resolution) > 0 group by did");

			if (rs != null) {
				report = new ArrayList<>();
				while (rs.next()) {
					to = new ReportTO();
					/*
					 * System.out.println("did : " + rs.getInt("id"));
					 * System.out.println("user : " + rs.getString("myuser"));
					 */
					to.setLabel(rs.getString("dept_name"));
					to.setValue(rs.getInt("diff"));
					report.add(to);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return report;
	}

	public void testdb() {
		Connection conn = getConnection();

		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from comments");

			while (rs.next()) {
				System.out.println("id : " + rs.getInt("id"));
				System.out.println("user : " + rs.getString("myuser"));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	// manish....................
	public boolean openComplaint(ComplaintTO to) {
		String query = "update complaints set status = 'open' where cid = ?";
		boolean status = false;
		Connection conn = getConnection();

		try {
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, to.getCid());

			int result = st.executeUpdate();

			if (result == 1) {
				System.out.println("1 row opened succesfully");
				status = true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return status;

	}

	public ArrayList<ComplaintTO> getNotification(String field, String key, int id, String userType) {

		Connection conn = getConnection();
		PreparedStatement ps = null;

		ArrayList<ComplaintTO> compls = new ArrayList<>();
		ComplaintTO to = null;

		try {
			ResultSet rs = null;

			ps = conn.prepareStatement("select * from user_notify,complaints where user_notify.cid=complaints.cid and user_notify.uid = ?");
			ps.setInt(1, id);

			rs = ps.executeQuery();

			while (rs.next()) {
				to = new ComplaintTO();
				to.setCid(rs.getInt("cid"));
				to.setDate_resolution(rs.getTimestamp("date_resolution"));
				to.setDate_submission(rs.getTimestamp("date_submission"));
				to.setDescription(rs.getString("notify_txt"));
				to.setDid(rs.getInt("did"));
				to.setUid(rs.getInt("uid"));
				to.setLocation(rs.getString("location"));
				to.setStatus(rs.getString("status"));
				to.setDid(rs.getInt("did"));

				compls.add(to);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compls;

	}

	// for department notification
	public ArrayList<ComplaintTO> getdeptNotification(String field, String key, int id, String userType) {

		Connection conn = getConnection();
		PreparedStatement ps = null;

		ArrayList<ComplaintTO> compls = new ArrayList<>();
		ComplaintTO to = null;

		try {
			ResultSet rs = null;

			ps = conn.prepareStatement("select * from dept_notify,complaints where dept_notify.cid=complaints.cid and dept_notify.did = ?");
			ps.setInt(1, id);

			rs = ps.executeQuery();

			while (rs.next()) {
				to = new ComplaintTO();
				to.setCid(rs.getInt("cid"));
				to.setDate_resolution(rs.getTimestamp("date_resolution"));
				to.setDate_submission(rs.getTimestamp("date_submission"));
				to.setDescription(rs.getString("notify_txt"));
				to.setDid(rs.getInt("did"));
				to.setUid(rs.getInt("uid"));
				to.setLocation(rs.getString("location"));
				to.setStatus(rs.getString("status"));
				to.setDid(rs.getInt("did"));

				compls.add(to);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compls;

	}

	// ........................

}
